package org.wwy.metadata;

import org.apache.commons.lang3.StringUtils;
import org.junit.Test;

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

public class MetaDataTest2 {

    @Test
    public void test() throws SQLException {
        Connection connection =
                DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test2?useSSL=false&serverTimezone=UTC",
                        "root", "123456");
        try {
            ;
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet rs = metaData.getPrimaryKeys("TEST2", null, "t_user1_1");

            List<MetaKeyInfo> metaKeyInfos = new ArrayList<>();
            while (rs.next()) {
                MetaKeyInfo keyInfo = new MetaKeyInfo();
                keyInfo.setName(rs.getString("PK_NAME"));
                keyInfo.setColumns(rs.getString("COLUMN_NAME"));
                keyInfo.setType("Primary");
                metaKeyInfos.add(keyInfo);
            }

            List<MetaKeyInfo> keyInfos = new ArrayList<>();
            Map<String, List<MetaKeyInfo>> collect = metaKeyInfos.stream().filter(t -> StringUtils.isNotEmpty(t.getName()) && StringUtils.isNotEmpty(t.getColumns())).collect(Collectors.groupingBy(MetaKeyInfo::getName));
            collect.keySet().stream().forEach(t -> {
                MetaKeyInfo keyInfo = new MetaKeyInfo();
                keyInfo.setName(t);
                keyInfo.setType("Primary");
                keyInfo.setColumns(String.join(",", collect.get(t).stream().map(MetaKeyInfo::getColumns).collect(Collectors.toList())));
                keyInfos.add(keyInfo);
            });
            System.out.println(keyInfos);
        } finally {
            if (connection != null) {
                connection.close();
            }
        }

    }

    @Test
    public void test2() throws SQLException {
        Connection connection =
                DriverManager.getConnection("jdbc:oracle:thin:@//10.150.8.30:1521/DEV",
                        "done_dev", "done_dev");
        try {
            ;
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet rs = metaData.getColumns(null, connection.getSchema(), "T_USER2","%");

            while (rs.next()) {
                ResultSetMetaData metaData1 = rs.getMetaData();
                for(int i=0;i<metaData1.getColumnCount();i++){
                    String columnName = metaData1.getColumnName(i + 1);
                    System.out.println(columnName+"="+rs.getString(i+1));
                }
                System.out.println("=================================");
            }

        } finally {
            if (connection != null) {
                connection.close();
            }
        }

    }




    /**
     * @param tableMetas         库1
     * @param standardTableMetas 库2 标准库
     * @param tableName
     */
    private void checkColumn(Map<String, List<ColumnInfo>> tableMetas,
                             Map<String, List<ColumnInfo>> standardTableMetas, String tableName) {
        List<ColumnInfo> tableColumns = tableMetas.get(tableName);

        List<ColumnInfo> stdColumns = standardTableMetas.get(tableName);

        for (ColumnInfo stdColumn : stdColumns) {
            String stdColumnName = stdColumn.getName();
            boolean exists = false;
            String outString = "";
            for (ColumnInfo info : tableColumns) {
                if (stdColumnName.equalsIgnoreCase(info.getName())) {
                    exists = true;
                    String type = info.getTypeName();
                    if (stdColumn.getTypeName().equalsIgnoreCase(type)) {
//                        System.out.println("类型一致");
                        Integer length = info.getLength();
                        if (stdColumn.getLength().equals(length)) {
//                            System.out.println("长度一致");
                        } else {
                            outString += "与标准库长度不一致:" + length + ":" + stdColumn.getLength() + "\n";
                        }
                        Integer scale = info.getScale();
                        if (stdColumn.getScale().equals(scale)) {
//                            System.out.println("小数位一致");
                        } else {
                            outString += "与标准库小数位不一致:" + scale + ":" + stdColumn.getScale() + "\n";
                        }
                    } else {
                        outString += "与标准库类型不一致:" + type + ":" + stdColumn.getTypeName() + "\n";
                    }
                    break;
                }
            }
            if (!exists) {
                outString += stdColumnName + "字段不包含!";
            }
            if (outString.length() > 0) {
                outString = "字段:" + stdColumnName + "\n" + outString;
                System.out.println(outString);
            }

        }

    }

    private Map<String, List<ColumnInfo>> getTableMeta(Connection connection) throws SQLException {
        Map<String, List<ColumnInfo>> allTableColumns = new LinkedHashMap<>();

        try {
            ;
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet rs = metaData.getColumns(connection.getCatalog(), connection.getSchema(), "%", "%");


            while (rs.next()) {
                int columnCount = rs.getMetaData().getColumnCount();
                String tableName = rs.getString("TABLE_NAME");
                List<ColumnInfo> columns = allTableColumns.get(tableName);
                if (columns == null) {
                    columns = new ArrayList<>();
                    allTableColumns.put(tableName.toUpperCase(), columns);
                } else {
                    columns = allTableColumns.get(tableName);
                }
                ColumnInfo columnInfo = new ColumnInfo();
                String columnName = rs.getString("COLUMN_NAME");
                columnInfo.setName(columnName);
                columnInfo.setTypeName(rs.getString("TYPE_NAME"));
                columnInfo.setLength(rs.getInt("COLUMN_SIZE"));
                columnInfo.setScale(rs.getInt("DECIMAL_DIGITS"));
                columns.add(columnInfo);
            }
            return allTableColumns;
        } finally {
            if (connection != null) {
                connection.close();
            }
        }

    }

    private void getTableMeta(Connection connection, String tableName, String colName) throws SQLException {
        Map<String, List<String>> allTableColumns = new LinkedHashMap<>();

        try {
            ;
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet rs = metaData.getColumns(connection.getCatalog(), connection.getSchema(), tableName, colName);


            while (rs.next()) {
                int columnCount = rs.getMetaData().getColumnCount();
                List<String> columns = allTableColumns.get(tableName);
                if (columns == null) {
                    columns = new ArrayList<>();
                    allTableColumns.put(tableName.toUpperCase(), columns);
                } else {
                    columns = allTableColumns.get(tableName);
                }
                for (int i = 0; i < columnCount; i++) {
                    System.out.println(rs.getMetaData().getColumnName(i + 1) + "=" + rs.getString(i + 1));
                }
                System.out.println("------------------------");
            }
        } finally {
            if (connection != null) {
                connection.close();
            }
        }

    }


    @Test
    public void testexport() throws SQLException, FileNotFoundException, UnsupportedEncodingException {
        Connection connection =
                DriverManager.getConnection("jdbc:oracle:thin:@//192.168.20.119:1521/orcl",
                        "cmsindicators", "cmsindicators");
        exportCsv(connection, "select * from mid_l2_prd where prd_code='000505'");
    }

    @Test
    public void testIndex() throws SQLException, FileNotFoundException, UnsupportedEncodingException {
        Connection connection =
                DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.93:1521:helowin",
                        "test3", "test3");
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet resultSet = metaData.getIndexInfo(connection.getCatalog(), connection.getSchema(),
                    "MID_L2_PRD_AST_TRD_DTL", true, false);
            while (resultSet.next()) {
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int count = resultSetMetaData.getColumnCount();
                for (int i = 0; i < count; i++) {
                    String columnName = resultSetMetaData.getColumnName(i + 1);
                    System.out.println(columnName + "=" + resultSet.getString(i + 1));
                }
            }

        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    private void exportCsv(Connection connection, String sql) throws SQLException, FileNotFoundException, UnsupportedEncodingException {
        Map<String, List<String>> allTableColumns = new LinkedHashMap<>();
        File file = new File("d:\\test.csv");
        //构建输出流，同时指定编码
        OutputStreamWriter ow = new OutputStreamWriter(new FileOutputStream(file), "gbk");
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            Integer skipindex = null;
            for (int i = 0; i < count; i++) {
                String columnName = metaData.getColumnName(i + 1);
                if ("FUND_MNGR_ID".equalsIgnoreCase(columnName)) {
                    skipindex = i;
                    continue;
                }
                ow.write(columnName);
                if (i + 1 < count) {
                    ow.write(",");
                }

            }
            ow.write("\r\n");
            while (resultSet.next()) {
                for (int i = 0; i < count; i++) {
                    if (skipindex != null && skipindex.intValue() == i) {
                        continue;
                    }
                    String value = resultSet.getString(i + 1);
                    if (value != null) {
//                        ow.write("\"");
                        ow.write(value);
//                        ow.write("\"");
                    }
                    if (i + 1 < count) {
                        ow.write(",");
                    }
                }
                ow.write("\r\n");
            }

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }

            try {
                ow.flush();
                ow.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }
}
